﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;

namespace MJCOMMEN_CS.DBUtility
{
    public class DBkeyitem {

     public DBkeyitem(string _fieldname,object _fieldvalue) {
         this.fieldname = _fieldname;
         this.fieldvalue = _fieldvalue.ToString();
        }
     public string fieldname;
     public string fieldvalue;
    
    }
    public abstract class DBopenhandler : IDisposable
    {
    public  DBopenhandler() {
          try
          {
              conn.Close();

          }
          catch { }
      
      }
      /// <summary>
      /// 条件表达式
      /// </summary>
      public string ConditionExpress = string.Empty;
      /// <summary>
      /// sql语句
      /// </summary>
      public string Sqlcmd=string.Empty;
      /// <summary>
      /// 表名称
      /// </summary>
      protected string tablename=string.Empty;
      /// <summary>
      /// 当前操作所涉及的字段名称
      /// </summary>
      protected string fieldname=string.Empty;
      /// <summary>
      /// 当前所使用的数据库连接。
      /// </summary>
      protected IDbConnection conn;
      /// <summary>
      /// 当前所使用的命令对象
      /// </summary>
      protected IDbCommand cmd;
      /// <summary>
      /// 当前所使用的数据库适配器
      /// </summary>
      protected IDbDataAdapter da;
      /// <summary>
      /// 用于存储字段/值配对。
      /// 关于数组学习http://blog.csdn.net/zhang_xinxiu/article/details/8657431
      /// 有很好的解答
      /// </summary>
      protected ArrayList alFieldItems = new ArrayList();
      /// <summary>
      /// 用于存储SQL语句中的查询参数。
      /// 就是一些已经确定的值
      /// </summary>
      protected ArrayList alSqlCmdParameters = new ArrayList();
      /// <summary>
      /// 用于存储条件表达式中的查询参数。
      /// 就是通过参数才传到sql语句的内容
      /// </summary>
      protected ArrayList alConditionParameters = new ArrayList();

      public void Reset() {
       this.alFieldItems.Clear();
       this.alSqlCmdParameters.Clear();
       this.alConditionParameters.Clear();
       this.ConditionExpress = string.Empty;
       this.Sqlcmd = string.Empty;

       this.cmd.Parameters.Clear();
       this.cmd.CommandText = string.Empty;
       this.cmd.CommandType = CommandType.Text;//默认非存储过程执行
      }
      /// <summary>
      /// 添加一个字段/值对到数组中。
      /// </summary>
      /// 用例：（1） doh.AddFieldItem("deleted", 0);
      ///     doh.Insert("member");
      /// <param name="_fieldName">字段名称。</param>
      /// <param name="_fieldValue">字段值。</param>

      public void add_alFieldItems(string _fieldName, object _fieldValue)
      {

          for (int i = 0; i < this.alFieldItems.Count; i++)
          {
              if (((DBkeyitem)this.alFieldItems[i]).fieldname == _fieldName)
              {
                  throw new ArgumentException(_fieldName + "不能重复赋值!");
              }
          }
          this.alFieldItems.Add(new DBkeyitem(_fieldName, _fieldValue));
      }
      /// <summary>
      /// 添加条件表达式中的查询参数到数组中。注意：当数据库连接为SqlServer时，
      /// 参数名称必须和SQL语句匹配。其它则必须保持添加顺序和ConditionExpress中参数顺序一致，否则会失效。
      /// </summary>
      /// 用例：（1）doh.SqlCmd = "select * from view_member where userid=@userid";
      /// doh.AddConditionParameter("@userid", userid);
      ///   （2）doh.SqlCmd = "select * from comimg where tbid=@id";
      ///     doh.AddConditionParameter("@id", id);
      ///     DataTable Dt = doh.GetDataTable();
      /// <param name="_conditionName">条件名称。</param>
      /// <param name="_conditionValue">条件值。</param>
      public void add_alConditionParameters(string _conditionName, object _conditionValue)
      {
          for (int i = 0; i < this.alConditionParameters.Count; i++)
          {
              if (((DBkeyitem)this.alConditionParameters[i]).fieldname == _conditionName)
              {
                  throw new ArgumentException("条件参数名\"" + _conditionName + "\"不能重复赋值!");
              }
          }
          this.alConditionParameters.Add(new DBkeyitem(_conditionName, _conditionValue));
      }


      public abstract void GenParameters();

        /// <summary>
      /// INSERT INTO 表名称 VALUES (值1, 值2,....)
      /// INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
        /// </summary>
        /// <param name="_tablename"></param>
        /// <returns></returns>
      public int Insert(string _tableName)
      {
          this.tablename = _tableName;
          this.fieldname = string.Empty;
          this.Sqlcmd = "insert into " + this.tablename + "(";
          string tempValues = " values(";
          for (int i = 0; i < this.alFieldItems.Count - 1; i++)
          {
              this.Sqlcmd += "" + ((DBkeyitem)alFieldItems[i]).fieldname + "";
              this.Sqlcmd += ",";
              tempValues += "@para";
              tempValues += i.ToString();

              tempValues += ",";
          }
          //这里是对最后一条数据的操作
          this.Sqlcmd += ((DBkeyitem)alFieldItems[alFieldItems.Count - 1]).fieldname;
          this.Sqlcmd += ") ";

          tempValues += "@para";
          tempValues += (alFieldItems.Count - 1).ToString();

          tempValues += ")";
          this.Sqlcmd += tempValues;
          this.cmd.CommandText = this.Sqlcmd;
          this.GenParameters();
          cmd.ExecuteNonQuery();
          int autoId = 0;
          return autoId;
      } 
      /// <summary>
      /// UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
      /// 根据当前alFieldItem数组中存储的字段/值和条件表达式所指定的条件来更新数据库中的记录，返回所影响的行数。
      /// </summary>
      /// <param name="_tableName">要更新的数据表名称。</param>
      /// <returns>返回此次操作所影响的数据行数。</returns>
      public int Update(string _tableName)
      {
          this.tablename = _tableName;
          this.fieldname = string.Empty;
          this.Sqlcmd = "UPDATE " + this.tablename + " SET ";
          for (int i = 0; i < this.alFieldItems.Count - 1; i++)
          {//如果你把名称/值存在alFieldItems里也要用到@标签通过@para加数字用法
              this.Sqlcmd += ((DBkeyitem)alFieldItems[i]).fieldname;
              this.Sqlcmd += "=";
              //UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson'
              //set后面是更新的列和值，where指那个行
              this.Sqlcmd += "@para";
              this.Sqlcmd += i.ToString();

              this.Sqlcmd += ",";
          }
          this.Sqlcmd += ((DBkeyitem)alFieldItems[alFieldItems.Count - 1]).fieldname;
          this.Sqlcmd += "=";

          this.Sqlcmd += "@para";
          this.Sqlcmd += (alFieldItems.Count - 1).ToString();
          if (this.ConditionExpress != string.Empty)
          {
              this.Sqlcmd += " where " + this.ConditionExpress;
          }
          this.cmd.CommandText = this.Sqlcmd;
          this.GenParameters();
          int effectedLines = this.cmd.ExecuteNonQuery();
          return effectedLines;
      }

      /// <summary>
      /// 执行SqlCmd中的SQL语句，参数由AddSqlCmdParameters指定，与ConditionExpress无关。
      /// </summary>
      /// <returns>返回此次操作所影响的数据行数。</returns>
      public int ExecuteSqlNonQuery()
      {
          this.cmd.CommandText = this.Sqlcmd;
          this.GenParameters();
          return this.cmd.ExecuteNonQuery();
      }
      ///<summary>
      /// 获取指定表，指定列，指定条件的第一个符合条件的值。
      /// </summary>
      /// <param name="_tableName">表名称。</param>
      /// <param name="_fieldName">字段名称。</param>
      /// <param name="_isField">是否纯字段名？</param>
      /// <returns>获取的值。如果为空则返回null。</returns>
      public object GetField(string _tableName, string _fieldName, bool _isField)
      {
          this.tablename = _tableName;
          this.fieldname = _fieldName;
          if (_isField)
              this.Sqlcmd = "select " + this.fieldname + " from " + this.tablename;
          else
              this.Sqlcmd = "select " + this.fieldname + " from " + this.tablename;
          if (this.ConditionExpress != string.Empty)
          {
              this.Sqlcmd = this.Sqlcmd + " where " + this.ConditionExpress;
          }
          this.cmd.CommandText = this.Sqlcmd;
          this.GenParameters();
          object ret = cmd.ExecuteScalar();
          // md.ExecuteScalar();返回结果集中的首行首列
          //返回的是在符合你SQL语句中的第一行第一例的数据 
          //如果你SQL语句查的是整张表，但是这个命令就只能返回这张表的第一行，第一例的数据
          if (ret == null) ret = (object)string.Empty;
          return ret;
      }
      public object GetField(string _tableName, string _fieldName)
      {
          return GetField(_tableName, _fieldName, true);
      }
      /// <summary>
      /// 根据当前指定的SqlCmd获取DataTable。如果ConditionExpress不为空则会将其清空，所以条件表达式需要包含在SqlCmd中。
      /// </summary>
      /// <returns>返回查询结果DataTable。</returns>
      public DataTable GetDataTable()
      {
          DataSet ds = this.GetDataSet();
          return ds.Tables[0];
      }
      /// <summary>
      /// 根据当前指定的SqlCmd获取DataSet。如果ConditionExpress不为空则会将其清空，所以条件表达式需要包含在SqlCmd中。
      /// </summary>
      /// <returns>返回查询结果DataSet。</returns>
      public DataSet GetDataSet()
      {
          //this.alConditionParameters.Clear();
          this.ConditionExpress = string.Empty;
          this.cmd.CommandText = this.Sqlcmd;
          this.GenParameters();
          DataSet ds = new DataSet();
          this.da.SelectCommand = this.cmd;
          this.da.Fill(ds);
          return ds;
      }
      /// <summary>
      /// 释放资源
      /// </summary>
      public void Dispose()
      {
          conn.Close();
          conn.Dispose();
      }
    }
}
